
with t_san_order_cnt as (
    select
        agent_code
         ,fran_code
         ,virt_code
         ,network_code
         ,store_code
         ,count(1) as san_order_cnt
         ,dt
    from
        (
            select
                ord.input_time
                 ,ord.waybill_id
                 ,net.agent_code
                 ,net.agent_name
                 ,net.fran_code
                 ,net.fran_name
                 ,net.virt_code
                 ,net.virt_name
                 ,ord.pick_network_code as network_code
                 ,ord.pick_network_name as network_name
                 ,ext.post_site_code as store_code
                 ,point.name as store_name
                 ,ext.post_site_address as store_address
                 ,point.business_id
                 ,ext.post_site_name as business_name
                 ,ord.dt
            from jms_dwd.dwd_yl_oms_oms_order_incre_dt ord
                     left join jms_dwd.dwd_yl_oms_order_third_ext_base_dt ext on ord.id=ext.order_id
                     left join jms_dim.dim_tab_agent_point_base point on ext.post_site_code=point.courier_station_code
                     inner join jms_ods.yl_lmdm_sys_dictionary dict on dict.dt='{{ execution_date | cst_ds }}' and dict.category_id in (84,85) and dict.is_enable=1  and ord.order_source_name = dict.name
                     left join jms_dim.dim_network_whole_massage net on net.code=ord.pick_network_code
            where ord.dt ='{{ execution_date | cst_ds }}'
              and ord.is_delete = 1
              and ord.proxy_area_code <> 'Test001'
              and ord.pick_network_code <> '888888'
        )san_order
    group by dt,agent_code,fran_code,virt_code,network_code,store_code
)
   ,base_detail as (
    select
        agent_code
         , agent_name
         , franchisee_code
         , franchisee_name
         , virt_code
         , virt_name
         , network_code
         , network_name
         , store_code
         , store_name
         , build_type
         , source_type
         , aa.key as index_name
         , split(aa.value,',')[0] as index_value
         , split(aa.value,',')[1] as index_value_fz
         , business_id
         , business_name
         , store_in_total
         --, stage.dimension_type
         --, stage.weight
         --, stage.target_value
         --, stage.base_value
    from
        (
            select
                max(agent_code     ) as agent_code
                 , max(agent_name     ) as agent_name
                 , max(franchisee_code) as franchisee_code
                 , max(franchisee_name) as franchisee_name
                 , max(virt_code      ) as virt_code
                 , max(virt_name      ) as virt_name
                 , network_code
                 , max(network_name   ) as network_name
                 , store_code
                 , max(store_name     ) as store_name
                 , max(business_id) as business_id
                 , max(business_name) as business_name
                 , max(build_type) as build_type
                 , max(source_type) as source_type
                 , max(store_in_total) as store_in_total
                 , map(  'loss_rate'              ,concat(nvl(sum(third_loss_rt),0),',',sum(third_loss_cnt))
                , 'daily_delivery_rate'     ,concat(nvl(sum(store_out_rt ),0),',',sum(store_out_total))
                , 'seven_day_delivery_rate' ,concat(nvl(sum(store_out7_rt),0),',',sum(store_out7_total))
                , 'customer_complaint_rate' ,concat(nvl(sum(complaint_rt ),0),',',sum(complaint_total))
                , 'post_station_door_rate'  ,concat(nvl(sum(writeback_rt ),0),',',sum(writeback_cnt))
                , 'interception_rate' ,concat(nvl(sum(intercept_rt ),0),',',sum(intercept_cnt))
                , 'delivery_rate' ,concat(nvl(sum(san_order_rt ),0),',',sum(san_order_cnt))
                , '24_h_delivery_rate' ,concat(nvl(sum(store_out24h_rt ),0),',',sum(store_over_total_24h))
                , 'master_control'   ,concat(if(max(build_type)='其他建设类型',-1,max(build_type)),',',if(max(build_type)='其他建设类型',-1,max(build_type)))
                ) indx
            from
                (
                    select
                        agent_code
                         ,max(agent_name) as agent_name
                         ,franchisee_code
                         ,max(franchisee_name) as  franchisee_name
                         ,virt_code
                         ,max(virt_name) as virt_name
                         ,network_code
                         ,max(network_name) as network_name
                         ,store_code
                         ,max(store_name) as store_name
                         ,max(business_id) business_id
                         ,max(business_name) business_name
                         ,max(build_type) as build_type
                         ,max(source_type) as source_type
                         ,nvl(sum(store_in_total),0)   as store_in_total
                         ,nvl(sum(store_out_total) ,0) as store_out_total
                         ,nvl(sum(store_out7_total),0) as store_out7_total
                         ,nvl(sum(complaint_total) ,0) as complaint_total
                         ,nvl(sum(writeback_cnt)   ,0) as writeback_cnt
                         ,nvl(sum(intercept_cnt)   ,0) as intercept_cnt
                         ,nvl(max(san_order_cnt)   ,0) as san_order_cnt
                         ,nvl(cast(sum(store_out_total)/sum(store_in_total) as decimal(16,4)),0) as store_out_rt  --当日出库率
                         ,nvl(cast(sum(store_out7_total)/sum(store_in7_total) as decimal(16,4)),0) as store_out7_rt   --七日出库率
                         ,nvl(cast(sum(complaint_total)/sum(store_in_total) as decimal(16,8)),0) as complaint_rt   --投诉率
                         ,nvl(cast(sum(writeback_cnt)/sum(store_in_total) as decimal(16,6)),0) as writeback_rt     --送货上门率
                         ,nvl(cast(sum(intercept_cnt)/sum(store_in_total) as decimal(16,6)),0) as intercept_rt
                         --两天的出库关联一天的寄件 ，是一样的值，取max没数据问题
                         ,nvl(cast(max(san_order_cnt)/sum(store_in_total) as decimal(16,6)),0) as san_order_rt
                         ,nvl(cast(sum(store_over_total_24h)/sum(store_in_total) as decimal(16,6)),0) as store_out24h_rt
                         ,nvl(sum(store_over_total_24h),0) as store_over_total_24h
                         , 0 as third_loss_cnt
                         , 0 as third_loss_rt
                    from
                        (
                            select
                                io.agent_code
                                 ,io.agent_name
                                 ,io.franchisee_code
                                 ,io.franchisee_name
                                 ,io.virt_code
                                 ,io.virt_name
                                 ,io.scansitecode as network_code
                                 ,io.inputsite as network_name
                                 ,io.store_code
                                 ,io.store_name
                                 ,io.business_id
                                 ,io.business_name
                                 ,io.build_type
                                 ,if(io.dt='{{ execution_date | cst_ds }}', io.store_in_total,0)  as store_in_total
                                 ,if(io.dt=date_add('{{ execution_date | cst_ds }}',-6), io.store_in_total,0)  as store_in7_total
                                 ,if(io.dt='{{ execution_date | cst_ds }}',io.store_over_total,0) as store_out_total
                                 ,if(io.dt=date_add('{{ execution_date | cst_ds }}',-6),io.seven_store_over_total,0) as store_out7_total
                                 ,if(io.dt='{{ execution_date | cst_ds }}', io.complaint_total,0) as complaint_total
                                 ,if(io.dt='{{ execution_date | cst_ds }}', io.writeback_cnt,0) as writeback_cnt
                                 ,if(io.dt='{{ execution_date | cst_ds }}', io.intercept_cnt,0) as intercept_cnt
                                 ,if(io.dt='{{ execution_date | cst_ds }}', io.store_over_total_24h,0) as store_over_total_24h
                                 ,san.san_order_cnt  as san_order_cnt
                                 ,io.source as source_type
                            from jms_dm.dm_network_end_dispatch_diff_rate io
                                     left join t_san_order_cnt san on san.network_code=io.scansitecode and san.store_code=io.store_code
                            where io.dt='{{ execution_date | cst_ds }}' or io.dt=date_add('{{ execution_date | cst_ds }}',-6) and source='驿站'
                        )t group by agent_code,franchisee_code,network_code,store_code,virt_code

                    union all

                    select
                        res_agent_code as agent_code
                         , res_agent_name as agent_name
                         , res_fran_code as franchisee_code
                         , res_fran_name as franchisee_name
                         , res_virt_code as virt_code
                         , res_virt_name as virt_name
                         , res_network_code as network_code
                         , res_network_name as network_name
                         , store_code
                         , store_name
                         , business_id
                         , business_name
                         , null as build_type
                         , null as source_type
                         , 0 as store_in_total
                         , 0 as store_out_total
                         , 0 as store_out7_total
                         , 0 as complaint_total
                         , 0 as writeback_cnt
                         , 0 as intercept_cnt
                         , 0 as san_order_cnt
                         , 0 as store_out_rt  --当日出库率
                         , 0 as store_out7_rt --七日出库率
                         , 0 as complaint_rt --投诉率
                         , 0 as writeback_rt
                         , 0 as intercept_rt
                         , 0 as san_order_st
                         , 0 as store_out24h_rt
                         , 0 as store_over_total_24h
                         ,third_loss_cnt
                         , cast(third_loss_cnt/agent_in_cnt as decimal(20,10)) as third_loss_rt
                    from jms_dm.dm_end_dispatch_third_loss_cnt_dt
                    where dt='{{ execution_date | cst_ds }}' and store_code is not null  and source='驿站'
                )t  group by network_code,store_code
        )tt lateral view explode(indx) aa as key,value
    )

insert overwrite table  bidefault.dm_end_dispatch_store_level_detail_dt
select
    mm.agent_code       --代理区编码
     , mm.agent_name       --代理区名称
     , mm.franchisee_code  --加盟商code
     , mm.franchisee_name  --加盟商name
     , mm.virt_code        --虚拟代理区code
     , mm.virt_name        --虚拟代理区name
     , mm.network_code     --网点编码
     , mm.network_name     --网点名称
     , mm.store_code       --门店code
     , mm.store_name       --门店name
     , mm.build_type       --建设类型
     , mm.index_name       --指标名称
     , mm.index_value      --指标值
     -- , mm.weight           --权重
     -- , mm.target_value     --目标值
     -- , mm.base_value       --基础值
     , mm.index_score      --指标得分
     , mm.store_score      --驿站得分
     , ss.indicator_item as score_level --驿站等级
     ,'{{ execution_date | cst_ds }}' as date_time
     , mm.business_id
     , mm.business_name
     , store_in_total      --门店入库量
     , index_value_fz      --率值分子
     , source_type         --类型 机柜 驿站
     ,'{{ execution_date | cst_ds }}' as dt
from (
         select
             agent_code
              , agent_name
              , franchisee_code
              , franchisee_name
              , virt_code
              , virt_name
              , network_code
              , network_name
              , business_id
              , business_name
              , store_code
              , store_name
              , build_type
              , index_name
              , index_value
              , weight
              , target_value
              , base_value
              , score as index_score
              , sum(score) over(partition by network_code,store_code ) as store_score
    , store_in_total
              , index_value_fz
              , source_type
         from
             (
                 select
                     base.agent_code
                      , base.agent_name
                      , base.franchisee_code
                      , base.franchisee_name
                      , base.virt_code
                      , base.virt_name
                      , base.network_code
                      , base.network_name
                      , base.business_id
                      , base.business_name
                      , base.store_code
                      , base.store_name
                      , base.build_type
                      , base.index_name
                      , base.index_value
                      , stage.weight
                      , stage.target_value
                      , stage.base_value
                      , cast(base.index_value*100/stage.target_value as decimal(16,2)) as xx
                      , case when base.index_name='master_control'     and base.index_value in (1,2)
                                 then stage.weight
                             when base.index_name='master_control'     and base.index_value = -1
                                 then 0
                             when stage.dimension_type='positive'  and base.index_value*100>=stage.target_value
                                 then stage.weight
                             when stage.dimension_type='positive'  and base.index_value*100<stage.target_value and base.index_value*100>base_value
                                 then cast((base.index_value*100-stage.base_value)/(stage.target_value-stage.base_value) as decimal(16,4))*stage.weight
                             when stage.dimension_type='positive'  and base.index_value*100<=stage.base_value
                                 then 0
                             when stage.dimension_type='negative' and base.index_value*100>stage.target_value and base.index_value*100<stage.base_value
                                 then cast((stage.base_value-base.index_value*100)/(stage.base_value-stage.target_value) as decimal(16,4))*stage.weight
                             when stage.dimension_type='negative' and base.index_value*100<=stage.target_value
                                 then stage.weight
                             when stage.dimension_type='negative' and base.index_value*100>=stage.base_value
                                 then 0
                     end as score
                      ,base.store_in_total
                      ,base.index_value_fz
                      ,base.source_type
                 from base_detail base
                          left join bidefault.tab_stage_indicator_cfg stage on stage.status=0 and stage.is_delete=1 and base.index_name =stage.indicator_field
             )m
     )mm left join bidefault.tab_stage_indicator_cfg ss on ss.status=0 and ss.is_delete=1 and ss.dimension_type='level'
    and ((ss.indicator_item!='A' and ss.target_value>mm.store_score and ss.base_value<=mm.store_score) or
         (ss.indicator_item='A' and ss.target_value>=mm.store_score and ss.base_value<=mm.store_score) )
    distribute by dt, abs(hash(mm.network_code)) % 3;
